config = require "lapis.config"
config.default_config.postgres = {backend: "pgmoon"}
config.reset true

db = require "lapis.db.postgres"
import Model from require "lapis.db.postgres.model"
import stub_queries, assert_queries, sorted_pairs from require "spec.helpers"

describe "lapis.db.model.relations", ->
  sorted_pairs!
  get_queries, mock_query, set_queries = stub_queries!

  with old = assert_queries
    assert_queries = (expected, opts) ->
      collected_queries = if type(opts) == "function"
        callback = opts
        opts = nil
        old_q = get_queries!
        query_buffer = {}
        set_queries query_buffer
        callback!
        with query_buffer
          set_queries old_q
      else
        get_queries!

      old expected, collected_queries, opts

  local models

  before_each ->
    models = {}
    package.loaded.models = models

  it "should make belongs_to getter", ->
    mock_query "SELECT", { { id: 101 } }

    models.Users = class extends Model
      @primary_key: "id"

    models.CoolUsers = class extends Model
      @primary_key: "user_id"

    class Posts extends Model
      @relations: {
        {"user", belongs_to: "Users"}
        {"cool_user", belongs_to: "CoolUsers", key: "owner_id"}
      }

    post = Posts!
    post.user_id = 123
    post.owner_id = 99

    assert post\get_user!
    assert post\get_user!

    post\get_cool_user!

    assert_queries {
      'SELECT * FROM "users" WHERE "id" = 123 LIMIT 1'
      'SELECT * FROM "cool_users" WHERE "user_id" = 99 LIMIT 1'
    }

  it "should make belongs_to getter with inheritance", ->
    mock_query "SELECT", { { id: 101 } }

    models.Users = class extends Model
      @primary_key: "id"

    class Posts extends Model
      @relations: {
        {"user", belongs_to: "Users"}
      }

      get_user: =>
        with user = super!
          user.color = "green"

    post = Posts!
    post.user_id = 123
    assert.same {
      id: 101
      color: "green"
    }, post\get_user!

  it "caches nil result from belongs_to_fetch", ->
    mock_query "SELECT", {}

    models.Users = class extends Model
      @primary_key: "id"

    class Posts extends Model
      @relations: {
        {"user", belongs_to: "Users"}
      }

    post = Posts!
    post.user_id = 123

    assert.same nil, post\get_user!
    assert.same nil, post\get_user!
    assert.same 1, #get_queries!

  it "fetch getter", ->
    called = 0

    class Posts extends Model
      @relations: {
        { "thing", fetch: =>
          called += 1
          "yes"
        }
      }

    post = Posts!
    post.user_id = 123

    assert.same "yes", post\get_thing!
    assert.same "yes", post\get_thing!
    assert.same 1, called

    assert_queries {}

  it "fetch with preload", ->
    called = 0

    class Posts extends Model
      @relations: {
        { "thing"
          fetch: => "yes"
          preload: (objects, opts) ->
            for object in *objects
              continue if object.skip_me
              object.thing = called
              called += 1
        }
      }

    one = Posts!

    two = Posts!
    two.skip_me = true

    three = Posts!
    four = Posts!

    Posts\preload_relations {one, two, three}, "thing"

    assert.same 0, one\get_thing!
    assert.same nil, two\get_thing!
    assert.same 1, three\get_thing!
    assert.same "yes", four\get_thing!

    import LOADED_KEY from require "lapis.db.model.relations"

    for item in *{one, two, three}
      assert.true item[LOADED_KEY].thing

    assert.true four[LOADED_KEY].thing

  it "fetch with preload & autogenerated getter", ->
    count = 0

    class Posts extends Model
      @relations: {
        {"thing"
          fetch: true
          preload: (objects) ->
            for item in *objects
              count += 1
              item.thing = count
        }
      }

    post1 = Posts!
    post2 = Posts!

    assert.same 1, post1\get_thing!
    assert.same 2, post2\get_thing!
    assert.same 1, post1\get_thing!
    assert.same 2, post2\get_thing!

  it "should make belongs_to getters for extend syntax", ->
    mock_query "SELECT", { { id: 101 } }

    models.Users = class extends Model
      @primary_key: "id"

    m = Model\extend "the_things", {
      relations: {
        {"user", belongs_to: "Users"}
      }
    }

    obj = m!
    obj.user_id = 101


    assert obj\get_user! == obj\get_user!

    assert_queries {
      [[SELECT * FROM "users" WHERE "id" = 101 LIMIT 1]]
    }

  it "should make has_one getter", ->
    mock_query "SELECT", { { id: 101 } }

    models.Users = class Users extends Model
      @relations: {
        {"user_profile", has_one: "UserProfiles"}
      }

    models.UserProfiles = class UserProfiles extends Model

    user = Users!
    user.id = 123
    user\get_user_profile!

    assert_queries {
      'SELECT * FROM "user_profiles" WHERE "user_id" = 123 LIMIT 1'
    }

  it "fails with composite primary key on has_one", ->
    mock_query "SELECT", { { id: 111, id2: 222 } }

    models.Users = class Users extends Model
      @primary_key: {"a_id", "b_id"}
      @relations: {
        {"user_profile", has_one: "UserProfiles"}
        {"user_profile_with_key", has_one: "UserProfiles", key: {
          id: "a_id"
          id2: "b_id"
        }}
      }

    models.UserProfiles = class UserProfiles extends Model

    user = Users!
    user.a_id = 111
    user.b_id = 222

    assert.has_error(
      -> user\get_user_profile!
      "Model UsersRelations has composite primary keys, you must specify column mapping directly with `key`"
    )

    assert.has_error(
      -> Users\preload_relations { user }, "user_profile"
      "Model UsersRelations has composite primary keys, you must specify column mapping directly with `key`"
    )

    user\get_user_profile_with_key!

    Users\preload_relations { user }, "user_profile_with_key"

    assert_queries {
      'SELECT * FROM "user_profiles" WHERE "id" = 111 AND "id2" = 222 LIMIT 1'
      'SELECT * FROM "user_profiles" WHERE ("id", "id2") IN ((111, 222))'
    }

  it "has_one with composite key and where", ->
    import preload from require "lapis.db.model"
    models.Notifications = class Notifications extends Model

    models.Followings = class Followings extends Model
      @primary_key: {"source_user_id", "object_type", "object_id"}
      @relations: {
        {"notification"
          has_one: "Notifications"
          key: {
            object_id: "source_user_id"
            user_id: "object_id"
          }
          where: {
            type: 2
            object_type: 1
          }
        }
      }

    f = models.Followings\load {
      source_user_id: 1
      object_type: 2
      object_id: 3
    }

    preload {f}, "notification"

    assert_queries {
      [[SELECT * FROM "notifications" WHERE ("object_id", "user_id") IN ((1, 3)) AND "object_type" = 1 AND "type" = 2]]
    }

  it "should make has_one getter with custom key", ->
    mock_query "SELECT", { { id: 101 } }

    models.UserData = class extends Model

    models.Users = class Users extends Model
      @relations: {
        {"data", has_one: "UserData", key: "owner_id"}
      }

    user = Users!
    user.id = 123
    assert user\get_data!

    assert_queries {
      'SELECT * FROM "user_data" WHERE "owner_id" = 123 LIMIT 1'
    }

  it "makes has_one getter with composite key", ->
    mock_query "SELECT", { { id: 101 } }

    models.UserPageData = class extends Model

    models.UserPage = class extends Model
      @relations: {
        {"data", has_one: "UserPageData", key: {
          "user_id", "page_id"
        }}
      }

    up = models.UserPage!
    up.user_id = 99
    up.page_id = 234

    assert up\get_data!

    up2 = models.UserPage!
    up2.user_id = nil
    up2.page_id = 'hello'

    assert up2\get_data!

    assert_queries {
      [[SELECT * FROM "user_page_data" WHERE "page_id" = 234 AND "user_id" = 99 LIMIT 1]]
      [[SELECT * FROM "user_page_data" WHERE "page_id" = 'hello' AND "user_id" IS NULL LIMIT 1]]
    }

  it "should make has_one getter key and local key", ->
    mock_query "SELECT", { { id: 101, thing_email: "leafo@leafo" } }

    models.Things = class extends Model

    models.Users = class Users extends Model
      @relations: {
        {"data", has_one: "Things", local_key: "email", key: "thing_email"}
      }

    user = Users!
    user.id = 123
    user.email = "leafo@leafo"
    assert user\get_data!

    assert_queries {
      [[SELECT * FROM "things" WHERE "thing_email" = 'leafo@leafo' LIMIT 1]]
    }

  it "makes has_one getter with where clause", ->
    mock_query "SELECT", { { id: 101, owner_id: 123 } }

    models.UserData = class extends Model

    models.Users = class Users extends Model
      @relations: {
        {"data", has_one: "UserData", key: "owner_id", where: { state: "good"} }
      }

    user = Users!
    user.id = 123
    assert user\get_data!

    Users\preload_relations { user }, "data"

    assert_queries {
      [[SELECT * FROM "user_data" WHERE "owner_id" = 123 AND "state" = 'good' LIMIT 1]]
      [[SELECT * FROM "user_data" WHERE "owner_id" IN (123) AND "state" = 'good']]
    }

  it "makes has_one getter with where db.clause", ->
    mock_query "SELECT", { { id: 101, owner_id: 123 } }

    models.UserData = class extends Model

    models.Users = class Users extends Model
      @relations: {
        {"data", has_one: "UserData", key: "owner_id", where: db.clause { owner_id: "oops", "deleted"} }
        {"living_data",
          has_one: "UserData"
          key: "owner_id"
          where: db.clause {
            deleted: false
            deleted_at: db.NULL
          }, operator: "OR"
        }
      }

    user = Users!
    user.id = 123
    assert user\get_data!

    Users\preload_relations { user }, "data"

    assert user\get_living_data!
    Users\preload_relations { user }, "living_data"

    assert_queries {
      [[SELECT * FROM "user_data" WHERE "owner_id" = 123 AND (deleted) AND "owner_id" = 'oops' LIMIT 1]]
      [[SELECT * FROM "user_data" WHERE "owner_id" IN (123) AND (deleted) AND "owner_id" = 'oops']]
      [[SELECT * FROM "user_data" WHERE "owner_id" = 123 AND (NOT "deleted" OR "deleted_at" IS NULL) LIMIT 1]]
      [[SELECT * FROM "user_data" WHERE "owner_id" IN (123) AND (NOT "deleted" OR "deleted_at" IS NULL)]]
    }

  it "makes has_one getter with composite key with custom local names", ->
    mock_query "SELECT", { { id: 101 } }

    models.UserPageData = class extends Model

    models.UserPage = class extends Model
      @relations: {
        {"data", has_one: "UserPageData", key: {
          user_id: "alpha_id"
          page_id: "beta_id"
        }}
      }

    up = models.UserPage!
    up.alpha_id = 99
    up.beta_id = 234

    assert up\get_data!

    assert_queries {
      'SELECT * FROM "user_page_data" WHERE "page_id" = 234 AND "user_id" = 99 LIMIT 1'
    }

  it "make has_many paginated getter", ->
    mock_query [[COUNT]], { { c: 10} }
    mock_query [["posts"]], { { id: 99, user_id: 1234 } }

    models.Posts = class extends Model
    models.Users = class extends Model
      @relations: {
        {"posts", has_many: "Posts"}
        {"more_posts", has_many: "Posts", where: {color: "blue"}}
        {"ordered_posts", has_many: "Posts", order: "created_at desc"}
      }

    user = models.Users!
    user.id = 1234

    -- empty relation
    assert_queries {
      'SELECT * FROM "posts" WHERE "user_id" = 1234 LIMIT 10 OFFSET 0'
      'SELECT * FROM "posts" WHERE "user_id" = 1234 LIMIT 10 OFFSET 10'
    }, ->
      user\get_posts_paginated!\get_page 1
      user\get_posts_paginated!\get_page 2

    -- relation with where clause
    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 AND "color" = 'blue' LIMIT 10 OFFSET 10]]
    }, ->
      user\get_more_posts_paginated!\get_page 2

    -- pager with customized where clause
    assert_queries {
      [[SELECT COUNT(*) AS c FROM "posts" where "user_id" = 1234 AND "age" = '10 days']]
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 AND "age" = '10 days' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 AND "age" = '10 days' LIMIT 10 OFFSET 10]]
    }, ->
      pager = user\get_posts_paginated(where: {
        age: "10 days"
      })

      pager\total_items!
      pager\get_page 1
      pager\get_page 2

    -- preloading relation with specified where: it overwrites the where clause is this the desired behavior?
    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" IN (1234) AND "limit" = 'suspend']]
    }, ->
      pager = models.Users\preload_relation {user}, "more_posts", where: {
        limit: "suspend"
      }

    -- pager with customized per_page
    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 LIMIT 44 OFFSET 88]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 LIMIT 44 OFFSET 0]]
    }, ->
      pager = user\get_posts_paginated per_page: 44
      pager\get_page 3
      pager\get_page!

    -- offset ordered paginator
    assert_queries {
      'SELECT * FROM "posts" where "user_id" = 1234 order by "posts"."id" ASC limit 10'
      'SELECT * FROM "posts" where "posts"."id" > 1023 and ("user_id" = 1234) order by "posts"."id" ASC limit 10'
      [[SELECT * FROM "posts" where ("posts"."created_at", "posts"."id") < ('2020-1-1', 238) and ("user_id" = 1234) order by "posts"."created_at" desc, "posts"."id" desc limit 10]]
      [[SELECT * FROM "posts" where "user_id" = 1234 AND NOT "deleted" order by "posts"."id" ASC limit 10]]
    }, ->
      user\get_posts_paginated(ordered: {"id"})\get_page!
      user\get_posts_paginated(ordered: {"id"})\get_page 1023

      user\get_posts_paginated(order: "desc", ordered: {"created_at", "id"})\get_page "2020-1-1", 238

      user\get_posts_paginated(ordered: {"id"}, where: { deleted: false })\get_page!

    -- relation with order
    assert_queries {
      'SELECT * FROM "posts" WHERE "user_id" = 1234 ORDER BY created_at desc LIMIT 10 OFFSET 0'
      'SELECT * FROM "posts" WHERE "user_id" = 1234 ORDER BY created_at desc LIMIT 10 OFFSET 23410'
      'SELECT COUNT(*) AS c FROM "posts" where "user_id" = 1234 '
    }, ->
      pager = user\get_ordered_posts_paginated!
      pager\get_page!
      pager\get_page 2342
      pager\total_items!

    -- relation with order, order overwritten by pager
    assert_queries {
      'SELECT * FROM "posts" WHERE "user_id" = 1234 ORDER BY id asc LIMIT 10 OFFSET 0'
      'SELECT * FROM "posts" WHERE "user_id" = 1234 ORDER BY id asc LIMIT 10 OFFSET 10'
      'SELECT * FROM "posts" WHERE "user_id" = 1234 ORDER BY created_at desc LIMIT 10 OFFSET 0'
    }, ->
      pager = user\get_ordered_posts_paginated order: "id asc"
      pager\get_page!
      pager\get_page 2

      -- this should disable the order
      pager2 = user\get_ordered_posts_paginated order: false
      pager2\get_page!


  it "make has_many getter", ->
    models.Posts = class extends Model
    models.Things = class extends Model

    models.Users = class extends Model
      @relations: {
        {"posts", has_many: "Posts"}
        {"more_posts", has_many: "Posts", where: {color: "blue"}}
        {"fresh_posts", has_many: "Posts", order: "id desc"}

        {"with_key", has_many: "Things", key: "object_id", order: "id desc"}

        {"with_key_where", has_many: "Things", key: "object_id", where: {
          {"object_type = ?", 1}
        }, order: "id desc"}
      }

    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1234]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 AND "color" = 'blue']]
      [[SELECT * FROM "posts" WHERE "user_id" = 1234 ORDER BY id desc]]
      [[SELECT * FROM "things" WHERE "object_id" = 1234 ORDER BY id desc]]
      [[SELECT * FROM "things" WHERE "object_id" = 1234 AND (object_type = 1) ORDER BY id desc]]
    }, ->
      user = models.Users\load id: 1234

      user\get_posts!
      user\get_posts!

      user\get_more_posts!
      user\get_fresh_posts!

      user\get_with_key!
      user\get_with_key_where!

  it "makes has many with db.clause", ->
    models.Posts = class extends Model
    models.Users = class extends Model
      @relations: {
        {"posts", has_many: "Posts"}

        {"green_posts", has_many: "Posts", where: { color: "green"} }
        {"blue_posts", has_many: "Posts", where: db.clause { color: "blue"} }
        {"purple_posts", has_many: "Posts", where: db.clause  { color: "purple", hue: "purple" }, operator: "OR" }
      }

    user = models.Users\load id: 1

    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'blue']]
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'blue' LIMIT 10 OFFSET 10]]
      [[SELECT * FROM "posts" WHERE "user_id" IN (1) AND "color" = 'blue']]
    }, ->
      user\get_blue_posts!
      user\get_blue_posts_paginated!\get_page 2

      models.Users\preload_relations { user }, "blue_posts"

    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND ("color" = 'purple' OR "hue" = 'purple')]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND ("color" = 'purple' OR "hue" = 'purple') LIMIT 10 OFFSET 20]]
      [[SELECT * FROM "posts" WHERE "user_id" IN (1) AND ("color" = 'purple' OR "hue" = 'purple')]]
    }, ->
      user\get_purple_posts!
      user\get_purple_posts_paginated!\get_page 3

      models.Users\preload_relations { user }, "purple_posts"

    -- preload with custom where overwrites the default where. This is undocumented behavior, and may not actually be desired
    assert_queries {
      [[SELECT a,b FROM "posts" WHERE "user_id" IN (1) AND "color" = 'green']]
      [[SELECT a,b FROM "posts" WHERE "user_id" IN (1) AND (random() > 0.5)]]
    }, ->
      user = models.Users\load id: 1
      models.Users\preload_relation { user }, "blue_posts", {
        fields: "a,b"
        where: {
          color: "green"
        }
      }

      models.Users\preload_relation { user }, "blue_posts", {
        fields: "a,b"
        where: db.clause { "random() > 0.5" }
      }


    -- where merging for when relation has no where
    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'green' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'green' LIMIT 10 OFFSET 0]]
    }, ->
      user = models.Users\load id: 1
      user\get_posts_paginated({
        where: db.clause {
          color: "green"
        }
      })\get_page!

      -- produces slightly different syntax due to how the clause is merged
      user\get_posts_paginated({
        where: {
          color: "green"
        }
      })\get_page!


    -- where merging for when relation clause is db.clause
    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'blue' AND "color" = 'green' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'blue' AND "color" = 'green' LIMIT 10 OFFSET 0]]
    }, ->
      user = models.Users\load id: 1
      user\get_blue_posts_paginated({
        where: db.clause {
          color: "green"
        }
      })\get_page!

      -- merging into db.clause is impossible, so it produces separate clauses
      user\get_blue_posts_paginated({
        where: {
          color: "green"
        }
      })\get_page!

    -- where merging for when relation clause is plain table
    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'yellow' AND "color" = 'green' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "color" = 'yellow' LIMIT 10 OFFSET 0]]
    }, ->
      user = models.Users\load id: 1
      user\get_green_posts_paginated({
        where: db.clause {
          color: "yellow"
        }
      })\get_page!

      -- produces slightly different syntax because a table is allowed to merge
      -- into a table this is to keep legacy behavior around since before
      -- clauses were introduced. In the future this should throw an error,
      user\get_green_posts_paginated({
        where: {
          color: "yellow"
        }
      })\get_page!

  -- This test is for cases for when added where clauses overwrites a field on
  -- the joining clause. There may be undefined/undocumented behavior, this
  -- test serves track if the generated queries change.
  it "overwriting relation conditions", ->
    models.Posts = class extends Model
    models.Users = class extends Model
      @relations: {
        {"theta_post", has_one: "Posts", where: db.clause { user_id: "theta" } }
        {"mu_post", has_one: "Posts", where: { user_id: "mu" } }


        {"alpha_posts", has_many: "Posts", where: db.clause { user_id: "alpha" } }
        {"beta_posts", has_many: "Posts", where: { user_id: "beta" } }
      }

    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "user_id" = 'theta' LIMIT 1]]
      [[SELECT * FROM "posts" WHERE "user_id" = 2 AND "user_id" = 'mu' LIMIT 1]]

      [[SELECT * FROM "posts" WHERE "user_id" IN (3) AND "user_id" = 'theta']]
      [[SELECT * FROM "posts" WHERE "user_id" IN (3) AND "user_id" = 'mu']]

      [[SELECT * FROM "posts" WHERE "user_id" IN (4) AND "user_id" = 'alpha']]
      [[SELECT * FROM "posts" WHERE "user_id" IN (5) AND "user_id" = 'beta']]
    }, ->
      models.Users\load(id: 1)\get_theta_post!
      models.Users\load(id: 2)\get_mu_post!

      models.Users\preload_relations {models.Users\load(id: 3)}, "theta_post", "mu_post"


      models.Users\preload_relation {models.Users\load(id: 4)}, "theta_post", {
        where: { user_id: "alpha" }
      }

      models.Users\preload_relation {models.Users\load(id: 5)}, "mu_post", {
        where: { user_id: "beta" }
      }

    assert_queries {
      [[SELECT * FROM "posts" WHERE "user_id" = 1 AND "user_id" = 'alpha']]
      [[SELECT * FROM "posts" WHERE "user_id" = 2 AND "user_id" = 'beta']]
      [[SELECT * FROM "posts" WHERE "user_id" = 3 AND "user_id" = 'alpha' AND "user_id" = 'zeta' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 4 AND "user_id" = 'omega' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 5 AND "user_id" = 'alpha' AND "user_id" = 'delta' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" = 6 AND "user_id" = 'epsilon' AND "user_id" = 'beta' LIMIT 10 OFFSET 0]]
      [[SELECT * FROM "posts" WHERE "user_id" IN (7) AND "user_id" = 'alpha']]
      [[SELECT * FROM "posts" WHERE "user_id" IN (7) AND "user_id" = 'beta']]
      [[SELECT * FROM "posts" WHERE "user_id" IN (8) AND "user_id" = 'mu']]
      [[SELECT * FROM "posts" WHERE "user_id" IN (9) AND "user_id" = 'theta']]
    }, ->
      models.Users\load(id: 1)\get_alpha_posts!
      models.Users\load(id: 2)\get_beta_posts!

      models.Users\load(id: 3)\get_alpha_posts_paginated(where: {user_id: "zeta"})\get_page!
      models.Users\load(id: 4)\get_beta_posts_paginated(where: {user_id: "omega"})\get_page!

      models.Users\load(id: 5)\get_alpha_posts_paginated(where: db.clause {user_id: "delta"})\get_page!
      models.Users\load(id: 6)\get_beta_posts_paginated(where: db.clause  {user_id: "epsilon"})\get_page!

      models.Users\preload_relations {models.Users\load(id: 7)}, "alpha_posts", "beta_posts"


      models.Users\preload_relation {models.Users\load(id: 8)}, "alpha_posts", {
        where: { user_id: "mu" }
      }

      models.Users\preload_relation {models.Users\load(id: 9)}, "beta_posts", {
        where: { user_id: "theta" }
      }

  it "makes has_many getter with composite key", ->
    mock_query "SELECT", {
      { id: 101, user_id: 99, page_id: 234 }
      { id: 102, user_id: 99, page_id: 234 }
    }

    models.UserPageData = class extends Model

    models.UserPage = class extends Model
      @relations: {
        {"data", has_many: "UserPageData", key: {
          "user_id", "page_id"
        }}
      }

    up = models.UserPage!
    up.user_id = 99
    up.page_id = 234

    assert.same {
      { id: 101, user_id: 99, page_id: 234 }
      { id: 102, user_id: 99, page_id: 234 }
    }, up\get_data!

    up2 = models.UserPage!
    up2.user_id = 99
    up2.page_id = nil
    assert up2\get_data!

    assert_queries {
      'SELECT * FROM "user_page_data" WHERE "page_id" = 234 AND "user_id" = 99'
      'SELECT * FROM "user_page_data" WHERE "page_id" IS NULL AND "user_id" = 99'
    }


  it "should create relations for inheritance", ->
    class Base extends Model
      @relations: {
        {"user", belongs_to: "Users"}
      }

    class Child extends Base
      @relations: {
        {"category", belongs_to: "Categories"}
      }

    assert Child.get_user, "expecting get_user"
    assert Child.get_category, "expecting get_category"
    assert.same nil, rawget Child, "get_user"

  describe "polymorphic belongs to", ->
    local Foos, Bars, Bazs, Items

    before_each ->
      models.Foos = class Foos extends Model
      models.Bars = class Bars extends Model
        @primary_key: "frog_index"

      models.Bazs = class Bazs extends Model

      Items = class Items extends Model
        @relations: {
          {"object", polymorphic_belongs_to: {
            [1]: {"foo", "Foos"}
            [2]: {"bar", "Bars"}
            [3]: {"baz", "Bazs"}
          }}
        }

    it "should model_for_object_type", ->
      assert Foos == Items\model_for_object_type 1
      assert Foos == Items\model_for_object_type "foo"

      assert Bars == Items\model_for_object_type 2
      assert Bars == Items\model_for_object_type "bar"

      assert Bazs == Items\model_for_object_type 3
      assert Bazs == Items\model_for_object_type "baz"

      assert.has_error ->
        Items\model_for_object_type 4

      assert.has_error ->
        Items\model_for_object_type "bun"

    it "should object_type_for_model", ->
      assert.same 1, Items\object_type_for_model Foos
      assert.same 2, Items\object_type_for_model Bars
      assert.same 3, Items\object_type_for_model Bazs

      assert.has_error ->
        Items\object_type_for_model Items

    it "should object_type_for_object", ->
      assert.same 1, Items\object_type_for_object Foos!
      assert.same 2, Items\object_type_for_object Bars!
      assert.same 3, Items\object_type_for_object Bazs

      assert.has_error ->
        Items\object_type_for_model {}

    it "should call getter", ->
      mock_query "SELECT", { { id: 101 } }

      for i, {type_id, cls} in ipairs {{1, Foos}, {2, Bars}, {3, Bazs}}
        item = Items\load {
          object_type: type_id
          object_id: i * 33
        }

        obj = item\get_object!

        obj.__class == cls

        obj2 = item\get_object!

        assert.same obj, obj2

      assert_queries {
        'SELECT * FROM "foos" WHERE "id" = 33 LIMIT 1'
        'SELECT * FROM "bars" WHERE "frog_index" = 66 LIMIT 1'
        'SELECT * FROM "bazs" WHERE "id" = 99 LIMIT 1'
      }

    it "preloads with empty object list", ->
      assert_queries { }, ->
        Items\preload_relation {}, "object"

    it "preloads", ->
      k = 0
      n = ->
        k += 1
        k

      items = {
        Items\load {
          object_type: 1
          object_id: n!
        }

        Items\load {
          object_type: 2
          object_id: n!
        }

        Items\load {
          object_type: 1
          object_id: n!
        }

        Items\load {
          object_type: 1
          object_id: n!
        }
      }

      Items\preload_relation items, "object"

      assert_queries {
        'SELECT * FROM "foos" WHERE "id" IN (1, 3, 4)'
        'SELECT * FROM "bars" WHERE "frog_index" IN (2)'
      }

    it "preloads with options (fields)", ->
      items = {
        Items\load {
          object_type: 1
          object_id: 111
        }

        Items\load {
          object_type: 2
          object_id: 112
        }

        Items\load {
          object_type: 3
          object_id: 113
        }
      }

      Items\preload_relation items, "object", fields: {
        bar: "a, b"
        baz: "c, d"
      }

      assert_queries {
        'SELECT * FROM "foos" WHERE "id" IN (111)'
        'SELECT a, b FROM "bars" WHERE "frog_index" IN (112)'
        'SELECT c, d FROM "bazs" WHERE "id" IN (113)'
      }

  it "finds relation", ->
    import find_relation from require "lapis.db.model.relations"

    class Posts extends Model
      @relations: {
        {"user", belongs_to: "Users"}
        {"cool_user", belongs_to: "CoolUsers", key: "owner_id"}
      }

    class BetterPosts extends Posts
      @relations: {
        {"tags", has_many: "Tags"}
      }

    assert.same {"user", belongs_to: "Users"}, (find_relation Posts, "user")
    assert.same nil, (find_relation Posts, "not there")
    assert.same {"cool_user", belongs_to: "CoolUsers", key: "owner_id"},
      (find_relation BetterPosts, "cool_user")

  describe "clear_loaded_relation", ->
    it "clears loaded relation cached with value", ->
      mock_query "SELECT", {
        {id: 777, name: "hello"}
      }
      models.Users = class Users extends Model

      class Posts extends Model
        @relations: {
          {"user", belongs_to: "Users"}
        }

      post = Posts\load {
        id: 1
        user_id: 1
      }

      post\get_user!
      post\get_user!

      assert.same 1, #get_queries!

      assert.not.nil post.user

      post\clear_loaded_relation "user"

      assert.nil post.user

      post\get_user!

      assert.same 2, #get_queries!

    it "clears loaded relation cached with nil", ->
      mock_query "SELECT", {}

      models.Users = class Users extends Model

      class Posts extends Model
        @relations: {
          {"user", belongs_to: "Users"}
        }

      post = Posts\load {
        id: 1
        user_id: 1
      }

      post\get_user!
      post\get_user!

      assert.same 1, #get_queries!

      post\clear_loaded_relation "user"
      post\get_user!

      assert.same 2, #get_queries!

  describe "preload_relations", ->
    it "preloads relations that return empty", ->
      mock_query "SELECT", {}

      models.Dates = class Dates extends Model
      models.Users = class Users extends Model
      models.Tags = class Tags extends Model

      class Posts extends Model
        @relations: {
          {"user", belongs_to: "Users"}
          {"date", has_one: "Dates"}
          {"tags", has_many: "Tags"}
        }

      post = Posts\load {
        id: 888
        user_id: 234
      }

      Posts\preload_relations {post}, "user", "date", "tags"

      assert_queries {
        [[SELECT * FROM "users" WHERE "id" IN (234)]]
        [[SELECT * FROM "dates" WHERE "post_id" IN (888)]]
        [[SELECT * FROM "tags" WHERE "post_id" IN (888)]]
      }

      import LOADED_KEY from require "lapis.db.model.relations"

      assert.same {
        user: true
        date: true
        tags: true
      }, post[LOADED_KEY]

      before_count = #get_queries!

      post\get_user!
      post\get_date!
      post\get_tags!

      assert.same, before_count, #get_queries!

    it "preloads has_many with preload_opts (order, fields, where)", ->
      models.Tags = class Tags extends Model

      class Posts extends Model
        @relations: {
          {"tags", has_many: "Tags", order: "a desc"}
          {"good_tags", has_many: "Tags", order: "a desc", where: {
            good: true
          }}
        }

      assert_queries {
        [[SELECT a,b FROM "tags" WHERE "post_id" IN (123) AND (count > 1) ORDER BY a desc]]
        [[SELECT a,b FROM "tags" WHERE "post_id" IN (123) AND (count > 2) ORDER BY b asc]]
      }, ->
        Posts\preload_relation {Posts\load id: 123}, "tags", {
          fields: "a,b"
          where: db.clause {
            "count > 1"
          }
        }

        Posts\preload_relation {Posts\load id: 123}, "tags", {
          fields: "a,b"
          order: "b asc"
          where: db.clause {
            "count > 2"
          }
        }

      assert_queries {
        [[SELECT name FROM "tags" WHERE "post_id" IN (123) AND "good" ORDER BY a desc]]
        [[SELECT name FROM "tags" WHERE "post_id" IN (123) AND (count > 200) ORDER BY a desc]]
      }, ->
        Posts\preload_relation {Posts\load id: 123}, "good_tags", {
          fields: "name"
        }

        -- it completely replaces the relation where clause
        Posts\preload_relation {Posts\load id: 123}, "good_tags", {
          fields: "name"
          where: db.clause {
            "count > 200"
          }
        }

    it "preloads has_many with composite key", ->
      mock_query "SELECT", {
        { id: 101, user_id: 99, page_id: 234 }
        { id: 102, user_id: 99, page_id: 234 }
        { id: 103, user_id: 100, page_id: 234 }
      }

      models.UserPageData = class extends Model

      models.UserPage = class UserPage extends Model
        @relations: {
          {"data", has_many: "UserPageData", key: {
            "user_id", "page_id"
          }}
        }

      user_pages = {
        UserPage\load {
          user_id: 99
          page_id: 234
        }

        UserPage\load {
          user_id: 100
          page_id: 234
        }

        UserPage\load {
          user_id: 100
          page_id: 300
        }
      }

      UserPage\preload_relation user_pages, "data"

      assert_queries {
        'SELECT * FROM "user_page_data" WHERE ("user_id", "page_id") IN ((99, 234), (100, 234), (100, 300))'
      }

      import LOADED_KEY from require "lapis.db.model.relations"
      for user_page in *user_pages
        assert.true user_page[LOADED_KEY].data

      assert.same {
        { id: 101, user_id: 99, page_id: 234 }
        { id: 102, user_id: 99, page_id: 234 }
      }, user_pages[1].data

      assert.same {
        { id: 103, user_id: 100, page_id: 234 }
      }, user_pages[2].data

      assert.same {}, user_pages[3].data

    it "preloads has_one with key and local_key", ->
      mock_query "SELECT", {
        { id: 99, thing_email: "notleafo@leafo" }
        { id: 101, thing_email: "leafo@leafo" }
      }

      models.Things = class extends Model

      models.Users = class Users extends Model
        @relations: {
          {"thing", has_one: "Things", local_key: "email", key: "thing_email"}
        }

      user = Users!
      user.id = 123
      user.email = "leafo@leafo"

      Users\preload_relations {user}, "thing"

      assert_queries {
        [[SELECT * FROM "things" WHERE "thing_email" IN ('leafo@leafo')]]
      }

      assert.same {
        id: 101
        thing_email: "leafo@leafo"
      }, user.thing

    it "preloads has_one with where", ->
      mock_query "SELECT", {
        { thing_id: 123, name: "whaz" }
      }

      models.Files = class Files extends Model

      class Things extends Model
        @relations: {
          {"beta_file"
            has_one: "Files"
            where: { deleted: false, whoa: db.NULL }
          }
        }

      thing = Things\load { id: 123 }
      Things\preload_relations { thing }, "beta_file"

      assert.same {
        thing_id: 123
        name: "whaz"
      }, thing.beta_file

      assert_queries {
        [[SELECT * FROM "files" WHERE "thing_id" IN (123) AND NOT "deleted" AND "whoa" IS NULL]]
      }

    it "preloads has_one with composite key", ->
      import LOADED_KEY from require "lapis.db.model.relations"

      mock_query "SELECT", {
        {id: 1, user_id: 11, page_id: 101}
      }

      models.UserPageData = class extends Model

      models.UserPage = class UserPage extends Model
        @relations: {
          {"data", has_one: "UserPageData", key: {
            "user_id", "page_id"
          }}
        }

      user_pages = {
        UserPage\load {
          user_id: 10
          page_id: 100
        }

        UserPage\load {
          user_id: 11
          page_id: 101
        }
      }

      UserPage\preload_relation user_pages, "data"

      assert_queries {
        [[SELECT * FROM "user_page_data" WHERE ("user_id", "page_id") IN ((10, 100), (11, 101))]]
      }

      assert.same {
        {
          user_id: 10
          page_id: 100
          [LOADED_KEY]: { data: true }
        }

        {
          user_id: 11
          page_id: 101
          data: {
            id: 1, user_id: 11, page_id: 101
          }
          [LOADED_KEY]: { data: true }
        }
      }, user_pages

    it "preloads has_one with preload opts", ->
      mock_query "SELECT", {
        { id: 101, user_id: 99, page_id: 234 }
        { id: 102, user_id: 99, page_id: 234 }
        { id: 103, user_id: 100, page_id: 234 }
      }

      models.UserPageData = class extends Model

      models.UserPage = class UserPage extends Model
        @relations: {
          {"data", has_one: "UserPageData", key: {
            "user_id", "page_id"
          }}
          {"real_data", has_one: "UserPageData", key: {
            "user_id", "page_id"
          }, where: { real: "yes" }}
        }

      user_pages = {
        UserPage\load {
          user_id: 99
          page_id: 234
        }
        UserPage\load {
          user_id: 100
          page_id: 234
        }
      }

      assert_queries {
        [[SELECT id FROM "user_page_data" WHERE ("user_id", "page_id") IN ((99, 234), (100, 234))]]
        [[SELECT * FROM "user_page_data" WHERE ("user_id", "page_id") IN ((99, 234), (100, 234)) AND "deleted"]]
      }, ->
        UserPage\preload_relation user_pages, "data", {
          fields: "id"
        }

        UserPage\preload_relation user_pages, "data", {
          where: db.clause {
            deleted: true
          }
        }

      assert_queries {
        [[SELECT id FROM "user_page_data" WHERE ("user_id", "page_id") IN ((99, 234), (100, 234)) AND "real" = 'yes']]
        [[SELECT * FROM "user_page_data" WHERE ("user_id", "page_id") IN ((99, 234), (100, 234)) AND "deleted"]]
      }, ->
        UserPage\preload_relation user_pages, "real_data", {
          fields: "id"
        }

        UserPage\preload_relation user_pages, "real_data", {
          where: db.clause {
            deleted: true
          }
        }

    it "preloads has_many with order and name", ->
      mock_query "SELECT", {
        { primary_thing_id: 123, name: "whaz" }
      }

      models.Tags = class Tags extends Model

      class Things extends Model
        @relations: {
          {"cool_tags"
            has_many: "Tags"
            order: "name asc"
            where: { deleted: false }
            key: "primary_thing_id"
          }
        }

      thing = Things\load { id: 123 }
      Things\preload_relations {thing}, "cool_tags"
      assert.same {
        { primary_thing_id: 123, name: "whaz" }
      }, thing.cool_tags

      assert_queries {
        [[SELECT * FROM "tags" WHERE "primary_thing_id" IN (123) AND NOT "deleted" ORDER BY name asc]]
      }

    it "preloads belongs_to with correct name", ->
      mock_query "SELECT", {
        { id: 1, name: "last" }
        { id: 2, name: "first" }
        { id: 3, name: "default" }
      }

      models.Topics = class Topics extends Model

      class Categories extends Model
        @relations: {
          {"last_topic", belongs_to: "Topics"}
          {"first_topic", belongs_to: "Topics"}
          {"topic", belongs_to: "Topics"}
        }

      cat = Categories\load {
        id: 1243
        last_topic_id: 1
        first_topic_id: 2
        topic_id: 3
      }

      Categories\preload_relations {cat}, "last_topic", "first_topic", "topic"
      assert.same 3, #get_queries!

      assert.same {
        id: 1
        name: "last"
      }, cat\get_last_topic!, cat.last_topic

      assert.same {
        id: 2
        name: "first"
      }, cat\get_first_topic!, cat.first_topic

      assert.same {
        id: 3
        name: "default"
      }, cat\get_topic!, cat.topic

      assert.same 3, #get_queries!

    it "preloads belongs_to with preload opts", ->
      mock_query "SELECT", {
        { id: 1, name: "last" }
        { id: 2, name: "first" }
        { id: 3, name: "default" }
      }

      models.Topics = class Topics extends Model

      class Categories extends Model
        @relations: {
          {"topic", belongs_to: "Topics"}
        }

      categories = {
        Categories\load {
          id: 1243
          topic_id: 3
        }
        Categories\load {
          id: 1243
          topic_id: 2
        }
      }

      assert_queries {
        [[SELECT id, name FROM "topics" WHERE "id" IN (3, 2)]]
      }, ->
        Categories\preload_relation categories, "topic", {
          fields: "id, name"
        }

    it "preloads has_one with correct name", ->
      mock_query "SELECT", {
        {user_id: 1, name: "cool dude"}
      }

      models.UserData = class UserData extends Model

      class Users extends Model
        @relations: {
          {"data", has_one: "UserData"}
        }

      user = Users\load id: 1
      Users\preload_relations {user}, "data"
      assert.same {user_id: 1, name: "cool dude"}, user.data, user\get_data!

    it "finds inherited preloaders", ->
      models.Users = class Users extends Model

      class SimplePosts extends Model
        @relations: {
          {"user", belongs_to: "Users"}
        }

      class JointPosts extends SimplePosts
        @relations: {
          {"second_user", belongs_to: "Users"}
        }

      p = JointPosts\load {
        id: 999
        user_id: 1
        second_user_id: 2
      }

      JointPosts\preload_relations {p}, "user", "second_user"

  describe "has_one", ->
    it "preloads when using custom keys", ->
      mock_query "SELECT", {
        {user_id: 100, name: "first"}
        {user_id: 101, name: "second"}
      }

      models.UserItems = class UserItems extends Model
        @primary_key: "user_id"

        @relations: {
          {"application", has_one: "ItemApplications", key: "user_id"}
        }

        new: (user_id) =>
          @user_id = assert user_id, "missing user id"

      models.ItemApplications = class ItemApplications extends Model
        id = 1
        new: (user_id) =>
          @user_id = assert user_id, "missing user id"
          @id = id
          id += 1

      ui = UserItems 100
      a = assert ui\get_application!, "expected to get relation"
      assert.same 100, a.user_id

      ui2 = UserItems 101
      UserItems\preload_relations {ui2}, "application"
      a = assert ui2.application, "expected to get relation"
      assert.same 101, a.user_id

      assert_queries {
        [[SELECT * FROM "item_applications" WHERE "user_id" = 100 LIMIT 1]]
        [[SELECT * FROM "item_applications" WHERE "user_id" IN (101)]]
      }

  describe "generic preload", ->
    local preload

    before_each ->
      import preload from require "lapis.db.model"

      models.Users = class Users extends Model
        @relations: {
          {"tags", has_many: "Tags"}
          {"user_data", has_one: "UserData"}
          {"account", belongs_to: "Accounts"}
        }

        new: (@id) =>
          assert @id, "missing id"

      models.Tags = class Tags extends Model
        @relations: {
          {"owner", has_one: "Users"}
        }

      models.UserData = class UserData extends Model
        @relations: {
          {"images", has_many: "Images"}
        }

      models.Accounts = class Accounts extends Model
        @relations: {
          {"image", belongs_to: "Images"}
        }

      models.Images = class Images extends Model

    it "preloads basic relations with no data", ->
      users = {
        models.Users\load {
          id: 10
          account_id: 99
        }

        models.Users\load {
          id: 12
          account_id: 15
        }
      }

      assert_queries {
        [[SELECT * FROM "tags" WHERE "user_id" IN (10, 12)]]
        [[SELECT * FROM "user_data" WHERE "user_id" IN (10, 12)]]
        [[SELECT * FROM "accounts" WHERE "id" IN (99, 15)]]
      }, ->
        preload users, "tags", "user_data", "account"


      assert_queries { }, ->
        preload users, tags: false -- this causes relation not to load, no query


      assert_queries {
        [[SELECT * FROM "tags" WHERE "user_id" IN (10, 12)]]
        [[SELECT * FROM "user_data" WHERE "user_id" IN (10, 12)]]
        [[SELECT * FROM "accounts" WHERE "id" IN (99, 15)]]
      }, ->
        -- support options and multiple args
        preload users, {"tags", "user_data"}, "account"

      assert_queries {
        [[SELECT * FROM "user_data" WHERE "user_id" IN (10, 12)]]
        [[SELECT * FROM "accounts" WHERE "id" IN (99, 15)]]
        [[SELECT * FROM "tags" WHERE "user_id" IN (10, 12)]]
      }, ->
        -- support multiple formats, empty tables
        preload users, {tags: true, {"user_data", "account", {}}, {}}

    it "handles error cases", ->
      user = models.Users\load {
        id: 10
        account_id: 99
      }

      mock_query [[SELECT %* FROM "tags"]], {
        models.Tags\load { user_id: 10, tag: "first" }
        models.Tags\load { user_id: 10, tag: "second" }
      }

      assert.has_error(
        -> preload { user }, true
        "preload: requested relation is an unknown type: boolean. Expected string, table or nil"
      )

      assert.has_error(
        -> preload { user }, "tags", false
        "preload: requested relation is an unknown type: boolean. Expected string, table or nil"
      )

      assert.has_error(
        -> preload { user }, nil, -> "uhh"
        "preload: requested relation is an unknown type: function. Expected string, table or nil"
      )

      assert.has_error(
        -> preload { user }, tags: { true }
        "preload: requested relation is an unknown type: boolean. Expected string, table or nil"
      )

      assert.has_error(
        -> preload { user }, tags: 5
        "preload: requested relation is an unknown type: number. Expected string, table or nil"
      )

      assert.has_error(
        -> preload { user }, { "what" }
        "Model Users doesn't have preloader for what"
      )

      assert.has_error(
        -> preload { user }, "what"
        "Model Users doesn't have preloader for what"
      )

      assert.has_error(
        -> preload { user }, {{{"what"}}}
        "Model Users doesn't have preloader for what"
      )

    it "preloads with loaded callback", ->
      users = {
        models.Users\load {
          id: 10
          account_id: 99
        }
      }

      mock_query [[SELECT %* FROM "tags"]], {
        { user_id: 10, tag: "first" }
        { user_id: 10, tag: "second" }
      }

      run = false

      preload users, tags: (tags) ->
        assert.same {
          { user_id: 10, tag: "first" }
          { user_id: 10, tag: "second" }
        }, tags

        -- objects should be passed after loading
        for o in *tags
          assert models.Tags.__base == getmetatable(o), "object should be tags instance"

        run = true

      assert run, "expected callback to run"

    it "preloads nested relations", ->
      mock_query [[SELECT %* FROM "tags"]], {
        {
          id: 252
          user_id: 10
        }
        {
          id: 311
          user_id: 10
        }
      }

      mock_query [[SELECT %* FROM "user_data"]], {
        {
          id: 32
          user_id: 10
        }
      }

      user = models.Users 10
      user.account_id = 99

      preload { user }, "account", {
        tags: { "owner" }
        user_data: {"images"}
      }

      assert_queries {
        [[SELECT * FROM "accounts" WHERE "id" IN (99)]]
        [[SELECT * FROM "images" WHERE "user_data_id" IN (32)]]
        [[SELECT * FROM "tags" WHERE "user_id" IN (10)]]
        [[SELECT * FROM "user_data" WHERE "user_id" IN (10)]]
        [[SELECT * FROM "users" WHERE "tag_id" IN (252, 311)]]
      }, sorted: true


    it "preloads with overlapping rows", ->
      mock_query [[SELECT %* FROM "accounts"]], {
        {
          id: 10
          image_id: 88
        }
        {
          id: 11
          image_id: 99
        }
      }

      mock_query [[SELECT %* FROM "images"]], {
        {
          id: 88
          filename: "cool.png"
        }
      }

      -- man users point to same account
      users = {
        models.Users\load { id: 1, account_id: 10 }
        models.Users\load { id: 2, account_id: 10 }
        models.Users\load { id: 3, account_id: 10 }
        models.Users\load { id: 4, account_id: 10 }
        models.Users\load { id: 5, account_id: 10 }
      }

      -- this is testing to prevent writing q query that repeats the row ids
      -- many times
      assert_queries {
        'SELECT * FROM "accounts" WHERE "id" IN (10)'
        'SELECT * FROM "images" WHERE "id" IN (88)'
      }, ->
        preload users, account: "image"

      for user in *users
        assert user.account, "user should have account"
        assert users[1].account == user.account, "accont is same object"
        assert user.account.image, "account should have image"


    it "preloads with overlapping rows", ->
      mock_query "SELECT", {
        { id: 101, user_id: 1, page_id: 10 }
        { id: 102, user_id: 1, page_id: 12 }
        { id: 103, user_id: 1, page_id: 33 }
      }

      models.UserPageData = class extends Model

      models.UserPage = class UserPage extends Model
        @relations: {
          {"data", has_many: "UserPageData", key: {
            "user_id", "page_id"
          }}
        }

      -- man users point to same account
      pages = {
        models.UserPage\load { user_id: 1, page_id: 10 }
        models.UserPage\load { user_id: 1, page_id: 10 }
        models.UserPage\load { user_id: 1, page_id: 10 }
        models.UserPage\load { user_id: 1, page_id: 12 }
        models.UserPage\load { user_id: 1, page_id: 12 }
      }

      assert_queries {
        [[SELECT * FROM "user_page_data" WHERE ("user_id", "page_id") IN ((1, 10), (1, 12))]]
      }, ->
        preload pages, "data"

    it "preloads nested fetch relations", ->
      models.Collections = class Collection extends Model
        @relations: {
          {"user",
            fetch: => {}
            preload: (collections) ->
              for c in *collections
                c.user = models.Users\load {
                  id: 10
                }
              true
          }

          {"things",
            many: true
            fetch: => {}
            preload: (collections) ->
              for c in *collections
                c.things = {
                  models.Users\load {
                    id: 11
                  }

                  models.Users\load {
                    id: 12
                  }
                }

              true

          }
        }

        new: (@id) =>
          assert @id, "missing id"

      collection = models.Collections 44
      preload { collection }, {
        user: "tags"
        things: { "user_data", tags: {} }
      }

      assert_queries {
        -- TODO: homogeneous preload should be able to merge these queries
        [[SELECT * FROM "tags" WHERE "user_id" IN (10)]]
        [[SELECT * FROM "tags" WHERE "user_id" IN (11, 12)]]
        [[SELECT * FROM "user_data" WHERE "user_id" IN (11, 12)]]
      }, sorted: true

    it "preloads a fetch that returns basic value", ->
      class Items extends Model
        @relations: {
          {"things",
            many: true
            fetch: => error "should not be called"
            preload: (items, ...) ->
              for item in *items
                item.things = {true, true, false}
          }


          {"thing",
            fetch: => error "should not be called"
            preload: (items, ...) ->
              for item in *items
                item.thing = true -- store a boolean
          }
        }

      items = {Items!}
      preload items, things: {}, thing: {}
      assert.same {true, true, false}, items[1].things
      assert.same true, items[1].thing


    it "passes preload opts to fetch relation", ->
      local preload_objects, preload_opts

      class Item extends Model
        @relations: {
          {"things",
            many: true
            fetch: => error "should not be called"
            preload: (...) ->
              preload_objects, preload_opts = ...
          }
        }


      items = {Item!}
      preload items, things: {
        [preload]: {
          fields: "blue"
          random: "option"
        }
      }

      assert.equal items[1], preload_objects[1]
      assert.nil preload_objects[2]

      assert.same {
        fields: "blue"
        random: "option"
      }, preload_opts

    it "with skip_included preload option #ddd", ->
      models.Items = class Items extends Model
        @relations: {
          {"parents", has_many: "Items", key: "parent_id"}
        }

      items = {
        Items\load { id: 123, parents: {} } -- this one already has it
        Items\load { id: 234 }
      }

      preload items, parents: {
        [preload]: { fields: "what", skip_included: true }
      }

      assert_queries {
        [[SELECT what FROM "items" WHERE "parent_id" IN (234)]]
      }

    describe "optional relations", ->
      it "single optional relation", ->
        class OtherThing extends Model
          new: (opts) =>
            for k,v in pairs opts
              @[k] = v

        class Thing extends Model
          @relations: {
            {"other_thing"
              fetch: => error "preload should not fetch"

              preload: (list) ->
                for item in *list
                  item.other_thing = OtherThing {
                    thing: item
                  }
            }
          }

        class EmptyThing extends Model

        assert.has_error(
          ->
            preload {EmptyThing!, EmptyThing!}, "other_thing"
          "Model EmptyThing doesn't have preloader for other_thing"
        )

        assert.has_error(
          ->
            preload {Thing!, Thing!}, "missing_thing"
          "Model Thing doesn't have preloader for missing_thing"
        )


        empty_things = {EmptyThing!, EmptyThing!}
        preload empty_things, "?other_thing"

        things = {Thing!, Thing!}
        preload things, "?missing_thing"


        preload things, other_thing: "?unknown_thing"

        assert.truthy things[1].other_thing
        assert.equal things[1], things[1].other_thing.thing

        assert.truthy things[2].other_thing
        assert.equal things[2], things[2].other_thing.thing

      it "many optional relation", ->
        class OtherThing extends Model
          new: (opts) =>
            for k,v in pairs opts
              @[k] = v

        class ThingConnector extends Model
          @relations: {
            {"the_thing"
              fetch: => error "preload should not fetch"

              preload: (list) ->
                for item in *list
                  item.the_thing = OtherThing {
                    parent: item
                  }
            }
          }

          new: (opts) =>
            for k,v in pairs opts
              @[k] = v

        class Thing extends Model
          @relations: {
            {"other_things"
              many: true
              fetch: => error "preload should not fetch"

              preload: (list) ->
                for item in *list
                  item.other_things = {
                    OtherThing {
                      idx: 1
                      parent: item
                    }
                    ThingConnector {
                      idx: 2
                      parent: item
                    }
                  }
            }
          }

        things = {Thing!, Thing!}
        preload things, other_things: "?the_thing"

        for thing_idx=1,2
          assert.truthy things[thing_idx].other_things

          for other_things_idx=1,2
            assert.equal things[thing_idx], things[thing_idx].other_things[other_things_idx].parent

          assert.nil things[thing_idx].other_things[1].the_thing

          -- the thing connector instance
          assert.truthy things[thing_idx].other_things[2].the_thing


      it "optional into more relations", ->
        class Friend extends Model
          new: (opts) =>
            for k,v in pairs opts
              @[k] = v

        class TheThing extends Model
          @relations: {
            {"friend"
              fetch: => error "preload should not fetch"
              preload: (items) ->
                for item in *items
                  item.friend = Friend {
                    parent: item
                  }
            }
          }

        class Thing extends Model
          @relations: {
            {"friend"
              fetch: => error "preload should not fetch"
              preload: (items) ->
                for item in *items
                  item.friend = Friend {
                    parent: item
                  }

            }
          }

        class Thong extends Model
          @relations: {
            {"the_things"
              fetch: => error "preload should not fetch"
              many: true
              preload: (items) ->
                for item in *items
                  item.the_things = {
                    TheThing parent: item
                    TheThing parent: item
                  }
            }
          }


        things = {Thing!, Thong!, Thong!}
        preload things, {
          "?friend"
          "?the_things": "friend"
        }

        do -- thing
          assert.truthy things[1].friend
          assert.nil things[1].the_things

          assert.equal things[1], things[1].friend.parent


        do -- thong 1
          assert.nil things[2].friend
          assert.truthy things[2].the_things

          for the_thing in *things[2].the_things
            assert.truthy the_thing.friend
            assert.equal the_thing, the_thing.friend.parent

        do -- thong 2
          assert.nil things[3].friend
          assert.truthy things[3].the_things

          for the_thing in *things[3].the_things
            assert.truthy the_thing.friend
            assert.equal the_thing, the_thing.friend.parent

  describe "computed keys", ->
    it "belongs_to fails on use of computed keys", ->
      import preload from require "lapis.db.model"
      assert.has_error(
        ->
          models.UserGroups = class UserGroups extends Model
          models.Users = class Users extends Model
            @relations: {
              {"user_group", belongs_to: "UserGroup", key: {
                group_id: (user) -> "group-#{user.name}"
              }}
            }

        "`belongs_to` relation doesn't support composite key or computed key, use `has_one` instead"
      )

    it "has_one with computed key", ->
      import preload from require "lapis.db.model"
      models.Users = class Users extends Model
        @relations: {
          {"user_profile", has_one: "UserProfiles", key: {
            user_id: (user) -> "uid-#{user.id}"
          }}
        }

      models.UserProfiles = class UserProfiles extends Model

      do
        user = Users!
        user.id = 123
        user\get_user_profile!

      do
        u1 = Users!
        u1.id = 123
        u2 = Users!
        u2.id = 999

        preload {u1, u2}, "user_profile"

      assert_queries {
        [[SELECT * FROM "user_profiles" WHERE "user_id" = 'uid-123' LIMIT 1]]
        [[SELECT * FROM "user_profiles" WHERE "user_id" IN ('uid-123', 'uid-999')]]
      }

    it "has_many with computed key", ->
      import preload from require "lapis.db.model"
      models.Users = class Users extends Model
        @relations: {
          {"tags", has_many: "UserTags", key: {
            user_id: (user) -> "uid-#{user.id}"
          }}
        }

      models.UserTags = class UserTags extends Model

      do
        user = Users!
        user.id = 123
        user\get_tags!

      do
        u1 = Users!
        u1.id = 123
        u2 = Users!
        u2.id = 999

        preload {u1, u2}, "tags"

      assert_queries {
        [[SELECT * FROM "user_tags" WHERE "user_id" = 'uid-123']]
        [[SELECT * FROM "user_tags" WHERE "user_id" IN ('uid-123', 'uid-999')]]
      }

    describe "has_many with computed key returning list", ->
      local Users, UserTags

      before_each ->
        models.Users = class Users extends Model
          @relations: {
            {"tags", has_many: "UserTags", key: {
              user_id: (user) -> db.list user.tag_owner_ids
            }}
          }

        models.UserTags = class UserTags extends Model


      it "fetches with getter", ->
        mock_query "SELECT", {
          { id: 101, user_id: 10 }
          { id: 102, user_id: 20 }
        }


        user = Users\load {
          tag_owner_ids: {10, 20}
        }

        assert.same {
          { id: 101, user_id: 10 }
          { id: 102, user_id: 20 }
        }, user\get_tags!

        assert_queries {
          [[SELECT * FROM "user_tags" WHERE "user_id" IN (10, 20)]]
        }

      it "preloads", ->
        mock_query "SELECT", {
          { id: 201, user_id: 20 }
          { id: 202, user_id: 30 }
          { id: 203, user_id: 40 }
        }

        import preload from require "lapis.db.model"
        users = {
          Users\load {
            tag_owner_ids: {20}
          }
          Users\load {
            tag_owner_ids: {30, 40}
          }
          Users\load {
            tag_owner_ids: {10, 20}
          }
        }

        preload users, "tags"

        assert_queries {
          [[SELECT * FROM "user_tags" WHERE "user_id" IN (20, 30, 40, 10)]]
        }

        assert.same {
          { id: 201, user_id: 20 }
        }, users[1].tags

        assert.same {
          { id: 202, user_id: 30 }
          { id: 203, user_id: 40 }
        }, users[2].tags

        assert.same {
          { id: 201, user_id: 20 }
        }, users[3].tags


